package com.bringway.p2p.dao.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.bringway.framework.dao.P2PJdbcDao;
import com.bringway.p2p.dao.P2pDbhkDao;

import net.sf.json.JSONObject;

@Repository
public class P2pDbhkDaoImpl extends P2PJdbcDao implements P2pDbhkDao {
	
	Logger logger = Logger.getLogger(this.getClass().getName());

	@Override
	public List<Map<String, Object>> queryDbsyffList(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if(StringUtils.isNotEmpty(startDate)){
			startDate=startDate+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(endDate)){
			endDate=endDate+" 23:59:59";
		}
		String sjhm=params.optString("sjhm");
		String gyh=params.optString("gyh");
		String tjr=params.optString("tjr");
		
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("START", start);
		paramMap.put("LIMIT", limit);
		paramMap.put("SJHM", sjhm);
		paramMap.put("TJR", tjr);
		paramMap.put("GYH", gyh);
		
		sql.append(" select t1.ID,t1.USER_NAME,t1.ZQ_ID,t1.TCJE,date_format(t1.TZSJ,'%Y-%m-%d %h:%i:%s') TZSJ,t1.TZ_SOURCE,t1.DB_HK_SYJE ");
		sql.append(" 	,date_format(t1.DB_HK_DATE,'%Y-%m-%d %h:%i:%s') DB_HK_DATE,t1.DB_HK_STATE,t1.REF_TRZXX_ID,t1.IS_ZRZQ,t2.SJHM,t2.TJR ");
		sql.append("	,t3.XMMC,t3.QX,t3.QX_MONTH ");
		sql.append(" FROM t_zhfw_tzrxx_dbtz_syhk t1,t_zhfw_user t2,t_zhfw_zqzrlb t3 ");
		sql.append("	where t1.USER_NAME = t2.USER_NAME  and t1.ZQ_ID = t3.ID ");
		sql.append(" 	and t2.DISABLED =1   ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND date_format(t1.DB_HK_DATE,'%Y-%m-%d %H:%i:%s') >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND date_format(t1.DB_HK_DATE,'%Y-%m-%d %H:%i:%s') <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(sjhm)){
			sql.append(" AND t2.SJHM like "+ "'%"+sjhm+"%'");
		}
		if(StringUtils.isNotEmpty(gyh)){
			sql.append(" AND t1.USER_NAME like "+ "'%"+gyh+"%'");
		}
		if(StringUtils.isNotEmpty(tjr)){
			sql.append(" AND t2.TJR = :TJR");
			paramMap.put("TJR", tjr);
		}
		sql.append(" order by t1.DB_HK_DATE desc, t1.TZSJ DESC ");
		if(0 != limit){
			sql.append(" LIMIT :START,:LIMIT");
		}
		logger.info("queryDbsyffList sql is : " + sql.toString());
		logger.info("queryDbsyffList paramMap is : " + paramMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForList(
				sql.toString(), paramMap);

	}

	@Override
	public int getDbsyffCount(JSONObject params) {
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if(StringUtils.isNotEmpty(startDate)){
			startDate=startDate+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(endDate)){
			endDate=endDate+" 23:59:59";
		}
		String sjhm=params.optString("sjhm");
		String gyh=params.optString("gyh");
		String tjr=params.optString("tjr");
		
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("SJHM", sjhm);
		paramMap.put("GYH", gyh);
		
		sql.append(" SELECT COUNT(*) ");
		sql.append(" FROM t_zhfw_tzrxx_dbtz_syhk t1,t_zhfw_user t2,t_zhfw_zqzrlb t3 ");
		sql.append("	where t1.USER_NAME = t2.USER_NAME  and t1.ZQ_ID = t3.ID ");
		sql.append(" 	and t2.DISABLED =1   ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND date_format(t1.DB_HK_DATE,'%Y-%m-%d %H:%i:%s') >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND date_format(t1.DB_HK_DATE,'%Y-%m-%d %H:%i:%s') <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(sjhm)){
			sql.append(" AND t2.SJHM like "+ "'%"+sjhm+"%'");
		}
		if(StringUtils.isNotEmpty(gyh)){
			sql.append(" AND t1.USER_NAME like "+ "'%"+gyh+"%'");
		}
		if(StringUtils.isNotEmpty(tjr)){
			sql.append(" AND t2.TJR = :TJR");
			paramMap.put("TJR", tjr);
		}
		logger.info("getDbsyffCount sql is : " + sql.toString());
		logger.info("getDbsyffCount paramMap is : " + paramMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForObject(sql.toString(),paramMap, Integer.class);

	}

	@Override
	public String getDbsyffSum(JSONObject params) {
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if(StringUtils.isNotEmpty(startDate)){
			startDate=startDate+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(endDate)){
			endDate=endDate+" 23:59:59";
		}
		String sjhm=params.optString("sjhm");
		String gyh=params.optString("gyh");
		String tjr=params.optString("tjr");
		
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("SJHM", sjhm);
		paramMap.put("GYH", gyh);
		
		sql.append(" select sum(t1.DB_HK_SYJE) S_DBSYJE ");
		sql.append(" FROM t_zhfw_tzrxx_dbtz_syhk t1,t_zhfw_user t2,t_zhfw_zqzrlb t3 ");
		sql.append("	where t1.USER_NAME = t2.USER_NAME  and t1.ZQ_ID = t3.ID ");
		sql.append(" 	and t2.DISABLED =1   ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND date_format(t1.DB_HK_DATE,'%Y-%m-%d %H:%i:%s') >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND date_format(t1.DB_HK_DATE,'%Y-%m-%d %H:%i:%s') <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(sjhm)){
			sql.append(" AND t2.SJHM like "+ "'%"+sjhm+"%'");
		}
		if(StringUtils.isNotEmpty(gyh)){
			sql.append(" AND t1.USER_NAME like "+ "'%"+gyh+"%'");
		}
		if(StringUtils.isNotEmpty(tjr)){
			sql.append(" AND t2.TJR = :TJR");
			paramMap.put("TJR", tjr);
		}
		logger.info("getDbsyffSum sql is : " + sql.toString());
		logger.info("getDbsyffSum paramMap is : " + paramMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForObject( sql.toString(), paramMap,  String.class);
	}
}
